from sql import sqlseverDB
from mini_tools.mytools import *
from config import *
import pandas as pd
import concurrent.futures as thddd
import copy

ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
datatype=2
currency='PHP'
pingtai='菲律宾虾皮'
size_img='http://198.12.127.45/size.jpg'

def pp_mbid():
    '''POD匹配模版'''    
    rs_mb= ms.ExecQuery('select did,dsearchtxt from mb where dtype=? and dxs=1',(datatype,))
    kwmb_dict={}
    for mbid,kwstr in rs_mb:
        cur_kws=[kitem.strip() for kitem in kwstr.split('|')]
        for kw in cur_kws:
            kwmb_dict[kw]=mbid

    rs=ms.ExecQuery('select itemid,pname from proshopeeph where id in (select min(id) from proshopeeph where datatype=? and pmbid=0 group by itemid)',(datatype,))
    task_count=len(rs)
    print(f'共{task_count}个产品待匹配模版')

    for itemid,pname in rs:

        for kw,mbid in kwmb_dict.items():
            if isContain(kw,pname):
                aff2=ms.ExecNoQuery('update proshopeeph set pmbid=? where itemid=?',(mbid,itemid))
                print(f'itemid:{itemid},匹配到模版ID:{mbid},保存状态:{aff2}')
                break
        
        task_count-=1

        print(f'任务队列剩余:{task_count}')

# 生成随机SKU ID
def generate_sku_id(length=10):
    characters = string.ascii_uppercase + string.digits
    sku_id = ''.join(random.choices(characters, k=length))
    return sku_id


# 上传SKU并执行插入数据库操作
def upload000(data: dict):
    new_skuid=generate_sku_id()
    data['skuid'] = new_skuid
    zdstr = ','.join(data.keys())
    parsms = list(data.values())
    wstr = ','.join(['?' for _ in parsms])
    sqlstr = f'insert into ProShopeePh({zdstr}) values({wstr})'
    aff = ms.ExecNoQuery(sqlstr, parsms)
    return new_skuid, aff

skuzhs2=[
    ('Black', '3XL'), ('Black', 'L'), ('Black', 'M'), ('Black', 'S'), ('Black', 'XL'), ('Black', 'XXL'),
    ('Blue', '3XL'), ('Blue', 'L'), ('Blue', 'M'), ('Blue', 'S'), ('Blue', 'XL'), ('Blue', 'XXL'),
    ('Red', '3XL'), ('Red', 'L'), ('Red', 'M'), ('Red', 'S'), ('Red', 'XL'), ('Red', 'XXL'),
    ('White', '3XL'), ('White', 'L'), ('White', 'M'), ('White', 'S'), ('White', 'XL'), ('White', 'XXL')
]

skuzhs=[
    ('Merah', 'S 155~160cm:40~50kg'),
    ('Merah', 'M 160~165cm:50~60kg'),
    ('Merah', 'L 165~170cm:60~70kg'),
    ('Merah', 'XL 170~175cm:70~80kg'),
    ('Merah', '2XL 175~180cm:80~90kg'),
    ('Merah', '3XL 180~190cm:90+kg'),
    ('Biru', 'S 155~160cm:40~50kg'),
    ('Biru', 'M 160~165cm:50~60kg'),
    ('Biru', 'L 165~170cm:60~70kg'),
    ('Biru', 'XL 170~175cm:70~80kg'),
    ('Biru', '2XL 175~180cm:80~90kg'),
    ('Biru', '3XL 180~190cm:90+kg'),
    ('Putih', 'S 155~160cm:40~50kg'),
    ('Putih', 'M 160~165cm:50~60kg'),
    ('Putih', 'L 165~170cm:60~70kg'),
    ('Putih', 'XL 170~175cm:70~80kg'),
    ('Putih', '2XL 175~180cm:80~90kg'),
    ('Putih', '3XL 180~190cm:90+kg'),
    ('Hitam', 'S 155~160cm:40~50kg'),
    ('Hitam', 'M 160~165cm:50~60kg'),
    ('Hitam', 'L 165~170cm:60~70kg'),
    ('Hitam', 'XL 170~175cm:70~80kg'),
    ('Hitam', '2XL 175~180cm:80~90kg'),
    ('Hitam', '3XL 180~190cm:90+kg')
]


def xc_update_sku(itemid,udatas):
    aff=0
    affdate=0
    for pid,images_str,sku1,sku2 in udatas:
        aff+=ms.ExecNoQuery('update proshopeeph set images=?,sku1=?,sku2=? where [id]=?',(images_str,sku1,sku2,pid))

    if aff==24:
        affdate=ms.ExecNoQuery('update proshopeeph set ispodupdate=1 where itemid=?',(itemid,))

    return f'itemid:{itemid},SKU更新:{aff}个,更改更新状态:{affdate}个'

def update_sku():
    '''POD生成固定sku'''
    rs_not = ms.ExecQuery('select distinct itemid from ProShopeePh where ispodcp=1 and datatype=2 and ispodupdate=0')
    print(f'共有 {len(rs_not)} 个 POD数据 待更改sku')
    new_datas=[]
    for r in rs_not:
        cur_itemid=r[0]
        rs_ddd=ms.ExecQuery('select id,images from ProShopeePh where itemid=?',(cur_itemid,))

        cdatas=[]
        for j in range(len(rs_ddd)):
            pid,images_str=rs_ddd[j]
            sku1,sku2=skuzhs[j]
            if size_img in images_str:
                imglist=json.loads(images_str)
                imglist.remove(size_img)
                images_str=json.dumps(imglist)
            cdatas.append([pid,images_str,sku1,sku2])
        print(f'itemid:{cur_itemid},更改{len(cdatas)}个sku')
        new_datas.append([cur_itemid,cdatas])

    print(f'共需修改:{len(new_datas)} 个POD 数据')
    task_count=len(new_datas)
    with thddd.ThreadPoolExecutor(max_workers=5) as t0:

        tasks=[t0.submit(xc_update_sku,xitemid,xdatas) for xitemid,xdatas in new_datas]
        
        for t in thddd.as_completed(tasks):
            try:
                res=t.result()
                print(res)
            except Exception as e:

                print(f'线程错误 => {e}')
            task_count-=1
            print(f'任务队列剩余:{task_count}')

def read_excel(file_path):
    # 读取 Excel 文件
    df = pd.read_excel(file_path,dtype={'店铺ID': str,'产品ID':str})
    df.rename(columns={'店铺ID': 'shopid','产品ID':'itemid','产品网址':'url','关键字名称':'GuanJianCi','产品类目':'catid',
                       '类目列表':'fe_categories','主题':'pname','当月销量':'skuSold','累计销量':'historical_sold',
                       '评分':'rating_star','评论数量':'cmt_count','单价':'price','图片列表':'images','视频列表':'video'}, inplace=True)
    # 将 NaN 值替换为 None
    df['skupirce'] = df['price']
    df['sold']=df['skuSold']
    df = df.applymap(lambda x: None if pd.isna(x) else x)

    # 将数据框转换为字典
    data_dict = df.to_dict(orient='records')

    return data_dict

  
def upload_xp_pod(pdata:dict):
    itemid=pdata['itemid']
    cur_imgages=json.loads(pdata['images'])
    first_img=cur_imgages[0]
    #cur_imgages[0]=size_img
    pdata['images']=json.dumps(cur_imgages)

    rs=ms.ExecQuery('select count(*) from proshopeeph where itemid=?',(itemid,))
    if rs[0][0]==0:
        
        aff1=0
        for sku1,sku2 in skuzhs:
            zds=list(pdata.keys())
            params=list(pdata.values())
            skuid=generate_sku_id()
            zds=zds+['sku1','sku2','skuid','image','currency','datatype','PingTai']
            params=params+[sku1,sku2,skuid,first_img,currency,datatype,pingtai]
            zdstr=','.join(zds)
            wstr=','.join(['?' for _ in params])
            sqlstr1=f'''insert into proshopeeph({zdstr}) values({wstr})'''
            aff1+=ms.ExecNoQuery(sqlstr1,params)
        print(f"itemid:{itemid},保存到《proshopeeph》状态:{aff1}")

    else:
        print(f'数据库已存在:{itemid}')

def dr_xp_pod():
    dir_path='普货'
    files=os.listdir(dir_path)
    print(f'共{len(files)}个文件待上传')
    for file_name in files:
        print(f'开始上传：《{file_name}》')
        cur_path=os.path.join(dir_path,file_name)
        datas=read_excel(cur_path)
        task_count=len(datas)
        print(f'任务总数剩余:{task_count}')
        with thddd.ThreadPoolExecutor(max_workers=20) as t:
            tasks=[t.submit(upload_xp_pod,data) for data in datas]
            for t0 in thddd.as_completed(tasks):
                try:
                    res=t0.result()
                except Exception as e:
                    print(f'线程错误:{e}')
                task_count-=1
                print(f'任务队列剩余:{task_count}')

        print(f'《{cur_path}》,上传完毕')


def up_load_sjm():

    rs=ms.ExecQuery('''select itemid,yntitle from proshopeeph where id in 
                    (select min(id) from proshopeeph  where yntitle is not null and yntitleRcode is  null group by itemid)''')
    task_cocunt=len(rs)
    j=1
    for r in rs:
        itemid,yntitle=r
        
        while True:
            new_sjm=generate_sku_id(8)
            rs_rcode= ms.ExecQuery('select count(*) from RandomCodes where RandomCode=?',(new_sjm,))
            if rs_rcode[0][0]==0:
                break
        aff1=ms.ExecNoQuery('update proshopeeph set yntitleRcode=? where itemid=?',(f'{yntitle}|{new_sjm}|',itemid,))
        aff2=0
        if aff1:
            aff2=ms.ExecNoQuery('insert into RandomCodes(itemid,RandomCode) values(?,?)',(itemid,new_sjm))
            
        print(f'第{j}个,itemid:{itemid},成功增加随机码:{new_sjm},状态aff1:{aff1},aff2:{aff2},剩余任务{task_cocunt-j}')
        j+=1
        
up_load_sjm()